package com.devenv.repository;

import com.devenv.entity.UserStatistics;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDate;
import java.util.List;
import java.util.Optional;

/**
 * 用户统计数据访问层
 * <p>
 * 提供用户统计实体的数据库访问操作
 * </p>
 * 
 * @author DevEnv OpenSource Team
 * @version 1.0.0
 * @since 1.0.0
 */
@Repository
public interface UserStatisticsRepository extends JpaRepository<UserStatistics, Long> {

    /**
     * 根据用户ID查找统计信息
     */
    Optional<UserStatistics> findByUserId(Long userId);

    /**
     * 检查用户统计是否存在
     */
    boolean existsByUserId(Long userId);

    /**
     * 查找脚本生成次数排行榜
     */
    @Query("SELECT us FROM UserStatistics us ORDER BY us.totalScriptsGenerated DESC")
    List<UserStatistics> findTopByScriptGeneration(Pageable pageable);

    /**
     * 查找模板创建次数排行榜
     */
    @Query("SELECT us FROM UserStatistics us ORDER BY us.totalTemplatesCreated DESC")
    List<UserStatistics> findTopByTemplateCreation(Pageable pageable);

    /**
     * 查找连续登录天数排行榜
     */
    @Query("SELECT us FROM UserStatistics us ORDER BY us.consecutiveLoginDays DESC")
    List<UserStatistics> findTopByConsecutiveLogins(Pageable pageable);

    /**
     * 查找活跃用户（最近登录）
     */
    @Query("SELECT us FROM UserStatistics us WHERE us.lastLoginDate >= :since ORDER BY us.lastLoginDate DESC")
    List<UserStatistics> findActiveUsersSince(@Param("since") LocalDate since);

    /**
     * 统计总的脚本生成次数
     */
    @Query("SELECT COALESCE(SUM(us.totalScriptsGenerated), 0) FROM UserStatistics us")
    long sumTotalScriptsGenerated();

    /**
     * 统计总的模板创建次数
     */
    @Query("SELECT COALESCE(SUM(us.totalTemplatesCreated), 0) FROM UserStatistics us")
    long sumTotalTemplatesCreated();

    /**
     * 统计总的模板使用次数
     */
    @Query("SELECT COALESCE(SUM(us.totalTemplatesUsed), 0) FROM UserStatistics us")
    long sumTotalTemplatesUsed();

    /**
     * 查找最受欢迎的操作系统
     */
    @Query("SELECT us.favoriteOs, COUNT(us) as count FROM UserStatistics us WHERE us.favoriteOs IS NOT NULL " +
           "GROUP BY us.favoriteOs ORDER BY count DESC")
    List<Object[]> findPopularOperatingSystems();

    /**
     * 查找最受欢迎的软件分类
     */
    @Query("SELECT us.favoriteCategory, COUNT(us) as count FROM UserStatistics us WHERE us.favoriteCategory IS NOT NULL " +
           "GROUP BY us.favoriteCategory ORDER BY count DESC")
    List<Object[]> findPopularCategories();

    /**
     * 查找新手用户（活动较少）
     */
    @Query("SELECT us FROM UserStatistics us WHERE " +
           "us.totalScriptsGenerated + us.totalTemplatesCreated + us.totalTemplatesUsed <= :threshold")
    List<UserStatistics> findNewbieUsers(@Param("threshold") Integer threshold);

    /**
     * 查找活跃用户（活动较多）
     */
    @Query("SELECT us FROM UserStatistics us WHERE " +
           "us.totalScriptsGenerated + us.totalTemplatesCreated + us.totalTemplatesUsed >= :threshold")
    List<UserStatistics> findActiveUsers(@Param("threshold") Integer threshold);

    /**
     * 按日期范围查找首次登录用户
     */
    @Query("SELECT us FROM UserStatistics us WHERE us.firstLoginDate BETWEEN :startDate AND :endDate")
    List<UserStatistics> findUsersByFirstLoginDateRange(@Param("startDate") LocalDate startDate, 
                                                        @Param("endDate") LocalDate endDate);

    /**
     * 查找长期活跃用户
     */
    @Query("SELECT us FROM UserStatistics us WHERE us.totalActiveDays >= :minDays AND us.maxConsecutiveLoginDays >= :minConsecutive")
    List<UserStatistics> findLongTermActiveUsers(@Param("minDays") Integer minDays, 
                                                @Param("minConsecutive") Integer minConsecutive);

    /**
     * 查找平均会话时长较长的用户
     */
    @Query("SELECT us FROM UserStatistics us WHERE us.avgSessionDurationMinutes >= :minDuration ORDER BY us.avgSessionDurationMinutes DESC")
    List<UserStatistics> findUsersWithLongSessions(@Param("minDuration") Integer minDuration);

    /**
     * 分页查询用户统计，支持排序
     */
    @Query("SELECT us FROM UserStatistics us WHERE " +
           "(:favoriteOs IS NULL OR us.favoriteOs = :favoriteOs) AND " +
           "(:favoriteCategory IS NULL OR us.favoriteCategory = :favoriteCategory)")
    Page<UserStatistics> findWithFilters(@Param("favoriteOs") String favoriteOs,
                                        @Param("favoriteCategory") String favoriteCategory,
                                        Pageable pageable);

    /**
     * 统计用户行为分布
     */
    @Query("SELECT " +
           "COUNT(CASE WHEN us.totalScriptsGenerated = 0 THEN 1 END) as noScripts, " +
           "COUNT(CASE WHEN us.totalScriptsGenerated BETWEEN 1 AND 10 THEN 1 END) as lowScripts, " +
           "COUNT(CASE WHEN us.totalScriptsGenerated BETWEEN 11 AND 50 THEN 1 END) as mediumScripts, " +
           "COUNT(CASE WHEN us.totalScriptsGenerated > 50 THEN 1 END) as highScripts " +
           "FROM UserStatistics us")
    Object[] getScriptGenerationDistribution();

    /**
     * 查找需要激活提醒的用户（长时间未登录）
     */
    @Query("SELECT us FROM UserStatistics us WHERE us.lastLoginDate < :threshold ORDER BY us.lastLoginDate ASC")
    List<UserStatistics> findInactiveUsers(@Param("threshold") LocalDate threshold);
}
